package com.sogoodsoft.web.crm.report;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oracle.jdbc.driver.OracleTypes;

import org.apache.struts2.convention.annotation.Result;
import org.apache.struts2.convention.annotation.Results;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import com.opensymphony.xwork2.ActionSupport;
import com.sogoodsoft.service.crm.report.ReportService;
import com.sogoodsoft.util.InfosysUtils;
import com.sogoodsoft.web.WebConstants;
import com.sogoodsoft.web.common.ListJRDataSource;
import com.sogoodsoft.web.common.Report;

@SuppressWarnings("serial")
@Results({
	@Result(name="COMPARE",location="compare.jsp")
	
})
public class VisitPerManMonthlyAction extends ActionSupport {

	
	private Logger logger = LoggerFactory.getLogger(getClass());
	private String ym;
	private Long org;
	private List<Object[]> list;
	@Autowired
	private ReportService service;

	@SuppressWarnings("deprecation")
	@Override
	public String execute() throws Exception {
	
		if(ym==null){ 
			Date now  = new Date();
			ym = InfosysUtils.getYear(now)+"-"+InfosysUtils.getMonth(now);			
			};
		if(org==null){
			org = new Long(0);
		}
		list = new ArrayList<Object[]>();
		
		if(WebConstants.mysql){
			 mysql();
		}else{
			oracle();
		}
		
		return SUCCESS;
	}
	
	public String compare()  throws Exception{
		if(ym==null){ 
			Date now  = new Date();
			ym = InfosysUtils.getYear(now)+"-"+InfosysUtils.getMonth(now);			
			};
		if(org==null){
			org = new Long(0);
		}
		list = new ArrayList<Object[]>();
		if(WebConstants.mysql){
			 mysql();
		}else{
			oracle();
		}
		return "COMPARE";
	}
	
	public void oracle() throws Exception{
		Connection conn =  service.getDao().getSession().connection();
		CallableStatement stat = conn.prepareCall("{call visit_per_man_monthly(?,?,?)}");
		logger.debug(ym);
		stat.setString(1,ym);
		stat.setLong(2, org);
		stat.registerOutParameter(3, OracleTypes.CURSOR);
		stat.execute();
		ResultSet rs = (ResultSet)stat.getObject(3);
		while(rs.next()){
			
			Object[] obj = {
					rs.getString(1),//姓名
					rs.getString(2),//部门名
					rs.getLong(3)//销量
					};
			//{rs.getString(1),rs.getString(2),rs.getString(3)};
			//logger.debug(String.valueOf(obj[1]));
			list.add(obj);
		}
		logger.debug(String.valueOf(list.size()));
		rs.close();
		stat.close();
		conn.close();
	}
	public void mysql()throws Exception{
		Connection conn =  service.getDao().getSession().connection();
		CallableStatement stat = conn.prepareCall("{call visit_per_man_monthly(?,?)}");
		logger.debug(ym);
		stat.setString(1,ym);
		stat.setLong(2, org);
		stat.execute();
		ResultSet rs = stat.getResultSet();
		while(rs.next()){
			
			Object[] obj = {
					rs.getString(1),//姓名
					rs.getString(2),//部门名
					rs.getLong(3)//销量
					};
			//{rs.getString(1),rs.getString(2),rs.getString(3)};
			//logger.debug(String.valueOf(obj[1]));
			list.add(obj);
		}
		logger.debug(String.valueOf(list.size()));
		rs.close();
		stat.close();
		conn.close();
	}
	@SuppressWarnings("unchecked")
	public String export() throws Exception {
		execute();
		logger.debug("ym："+ym);
		logger.debug("org："+org);
		ListJRDataSource jrs = new ListJRDataSource(list,2);
		Report report = new Report();
		report.ext = Report.EXT.excel;
		report.jasper="VisitPerManMonthly.jasper";
		report.storeName = "回访量对比";
		String mth = ym.substring(ym.indexOf("-")+1);
		String year = ym.replace("-"+mth, "");
		String depart = "";
		if(list.size()>0){
			depart = String.valueOf(list.get(0)[1]);
		}
		report.para.put("TITLE", year+"年"+mth+"月"+depart+report.storeName);
		return report.make(jrs);
	}
	public List<Object[]> getList() {
		return list;
	}

	public void setList(List<Object[]> list) {
		this.list = list;
	}

	public String getYm() {
		return ym;
	}

	public void setYm(String ym) {
		this.ym = ym;
	}

	public Long getOrg() {
		return org;
	}

	public void setOrg(Long org) {
		this.org = org;
	}
	
	
}
